<!DOCTYPE html>
<html lang="zh-cn">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>openGauss Blog  | openGauss中的并行回放以及和PG的对比</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">

    
    <link rel="shortcut icon" href="https://xzx666.gitee.io/lookeng/img/favicon.ico" type="image/x-icon" />
    <link rel="apple-touch-icon" href="https://xzx666.gitee.io/lookeng/img/apple-touch-icon.png" />

    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

    
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
    
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/commen.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/blog.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/mobile.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/pagination.css?t=1607593672000">
    
    <script>
      var _hmt = _hmt || [];
      (function() {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?34e9f585f29581007941aa1698181871";
        var s = document.getElementsByTagName("script")[0]; 
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
</head>
<body>
  <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
  <script src='//unpkg.com/valine/dist/Valine.min.js'></script>






<div class="container">
    <div class="columns blog-detail">
        <div class="post_detail">
            <div class="is-child box">
                <div class="breadCrumb"><a href="\zh\">博客/</a></div>
                <div class="blog-detail-header">
                    <h1>openGauss中的并行回放以及和PG的对比</h1>
                    <div class="blog-detail-prop">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-date.png">
                        <span class="article_right_date">2020-11-17</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-author.png">
                        <span class="article_right_author">数据库架构之美</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-view.png">
                        <span id="/zh/post/shujukujiagouzhimei/opengauss%E4%B8%AD%E7%9A%84%E5%B9%B6%E8%A1%8C%E5%9B%9E%E6%94%BE%E4%BB%A5%E5%8F%8A%E5%92%8Cpg%E7%9A%84%E5%AF%B9%E6%AF%94/" class="leancloud_visitors" data-flag-title="openGauss中的并行回放以及和PG的对比">
                            <i class="leancloud-visitors-count"></i>                     
                        </span>
                    </div>
                    <div class="blog-detail-tags">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-tag.png">
                        
                        <a href="https://xzx666.gitee.io/lookeng/zh/tags/opengauss%E4%B8%AD%E7%9A%84%E5%B9%B6%E8%A1%8C%E5%9B%9E%E6%94%BE%E4%BB%A5%E5%8F%8A%E5%92%8Cpg%E7%9A%84%E5%AF%B9%E6%AF%94">openGauss中的并行回放以及和PG的对比</a>
                        
                    </div>
                </div>
                <div class="content blog-content">
                    

<h1 id="opengauss中的并行回放以及和pg的对比-a-name-zh-cn-topic-0291959521-a">openGauss中的并行回放以及和PG的对比<a name="ZH-CN_TOPIC_0291959521"></a></h1>

<p>openGauss在非极致rto特性下通过recovery_max_workers和recovery_parallelism参数控制并行回放的线程数。下面测试一下效果，可以通过停止备库来模拟主库xlog堆积，此处统一模拟堆积到1000个xlog后启动备库，来进行应用和回放，最终统计回放完成时间。可以通过比对receiver_replay_location和receiver_flush_location之间的差距来判断是否回放完成。</p>

<p>由于recovery_max_workers和recovery_parallelism参数具有联动性，设置一个另外一个会自动设置为相同的值，此处只设置recovery_max_workers。</p>

<h2 id="recovery-max-workers-1-a-name-section92269010480-a">recovery_max_workers=1<a name="section92269010480"></a></h2>

<p>停止备库，启动压测程序，待xlog达到1000时关闭压测程序。</p>

<pre><code>[omm@db01 gaussdata]$ ll pg_xlog/ |wc -l
1000
[omm@db02 pg_xlog]$ gs_ctl start -M standby
[omm@db02 pg_xlog]$ date
Wed Sep 16 15:26:53 CST 2020
</code></pre>

<p>可以看到处于catchup过程：</p>

<pre><code>[omm@db02 pg_xlog]$ gs_ctl query
[2020-09-16 15:27:12.958][213287][][gs_ctl]: gs_ctl query ,datadir is (null)
 HA state:           
        local_role                     : Standby
        static_connections             : 2
        db_state                       : Catchup
        detail_information             : Normal


 Senders info:       
No information
 Receiver info:      
        receiver_pid                   : 211639
        local_role                     : Standby
        peer_role                      : Primary
        peer_state                     : Normal
        state                          : Catchup
        sender_sent_location           : 3C/28800000
        sender_write_location          : 3D/EE528328
        sender_flush_location          : 3D/EE528328
        sender_replay_location         : 3D/EE528328
        receiver_received_location     : 3C/28000000
        receiver_write_location        : 3C/27000000
        receiver_flush_location        : 3C/27000000
        receiver_replay_location       : 3A/8F4A9910
        sync_percent                   : 97%
        channel                        : 192.168.1.2:48458&lt;--192.168.1.1:5533
</code></pre>

<p>过一段时间发现追平了，但是receiver_replay_location和receiver_flush_location之间有差距，持续观察：</p>

<pre><code>[omm@db02 pg_xlog]$ gs_ctl query[2020-09-16 15:32:08.432][237296][][gs_ctl]: gs_ctl query ,datadir is (null) HA state:                   local_role                     : Standby        static_connections             : 2        db_state                       : Normal        detail_information             : Normal Senders info:       No information Receiver info:              receiver_pid                   : 211639        local_role                     : Standby        peer_role                      : Primary        peer_state                     : Normal        state                          : Normal        sender_sent_location           : 3D/FC647630        sender_write_location          : 3D/FC647630        sender_flush_location          : 3D/FC647630        sender_replay_location         : 3D/FC647630        receiver_received_location     : 3D/FC647630        receiver_write_location        : 3D/FC647630        receiver_flush_location        : 3D/FC647630        receiver_replay_location       : 3D/FC647630        sync_percent                   : 100%        channel                        : 192.168.1.2:48458&lt;--192.168.1.1:5533


[omm@db02 pg_xlog]$ date
Wed Sep 16 15:32:09 CST 2020
</code></pre>

<p>总共耗时：316s</p>

<h2 id="recovery-max-workers-8-a-name-section9472142075310-a">recovery_max_workers=8<a name="section9472142075310"></a></h2>

<pre><code>[omm@db01 gaussdata]$ ll pg_xlog |wc -l
1002
</code></pre>

<p>启动一个备库，开始追数和回放。</p>

<pre><code>[omm@db02 gaussdata]$ date
Thu Sep 17 09:32:59 CST 2020
[omm@db02 gaussdata]$ gs_ctl query
[2020-09-17 09:33:02.663][53466][][gs_ctl]: gs_ctl query ,datadir is (null)
 HA state:           
        local_role                     : Standby
        static_connections             : 2
        db_state                       : Catchup
        detail_information             : Normal


 Senders info:       
No information
 Receiver info:      
        receiver_pid                   : 53065
        local_role                     : Standby
        peer_role                      : Primary
        peer_state                     : Normal
        state                          : Catchup
        sender_sent_location           : 44/65800000
        sender_write_location          : 47/A600A858
        sender_flush_location          : 47/A600A858
        sender_replay_location         : 47/A600A858
        receiver_received_location     : 44/65800000
        receiver_write_location        : 44/65000000
        receiver_flush_location        : 44/65000000
        receiver_replay_location       : 44/432AFCC8
        sync_percent                   : 95%
        channel                        : 192.168.1.2:38322&lt;--192.168.1.1:5533
</code></pre>

<p>稳定后：</p>

<pre><code>[omm@db02 gaussdata]$ gs_ctl query
[2020-09-17 09:41:05.963][93661][][gs_ctl]: gs_ctl query ,datadir is (null)
 HA state:           
        local_role                     : Standby
        static_connections             : 2
        db_state                       : Normal
        detail_information             : Normal


 Senders info:       
No information
 Receiver info:      
        receiver_pid                   : 53065
        local_role                     : Standby
        peer_role                      : Primary
        peer_state                     : Normal
        state                          : Normal
        sender_sent_location           : 47/AF961308
        sender_write_location          : 47/AF961308
        sender_flush_location          : 47/AF961308
        sender_replay_location         : 47/AF961308
        receiver_received_location     : 47/AF961308
        receiver_write_location        : 47/AF961308
        receiver_flush_location        : 47/AF961308
        receiver_replay_location       : 47/AF961308
        sync_percent                   : 100%
        channel                        : 192.168.1.2:38322&lt;--192.168.1.1:5533


[omm@db02 gaussdata]$ date
Thu Sep 17 09:41:07 CST 2020
</code></pre>

<p>总耗时：428s</p>

<p>可以看到并行回放并没有起到作用，甚至开启多线程回放后反而回放变慢了。</p>

<p>测试过程中大致看了下日志产生速度：70M/s，插入的数据每秒6万条，而pg中在插入每秒6万条的时候，日志产生速度只有35M/s左右，是openGauss的一半，这块也需要优化。在极限测试情况下，在150并发时，pg的日志产生速度大概70M/s，tps大概是每秒13万行的插入，此时replay_lag确实在逐渐增大，说明在这个大小的日志产生速度下，pg已经回放不过来这么大量的日志。openGauss在6万tps插入时已经达到这么大日志量。</p>

<p><img src="../figures/modb_ef290bfc-0cea-11eb-bd37-38f9d3cd240d.png" alt="" /></p>

<p>所以从上面的表格以及测试结果可以看到首先开启并行回放参数不会加快回放速度，甚至回放速度回变慢，openGauss备库回放跟不上的原因并不在于openGauss的回放速度比pg慢，回放速度他俩是差不多的，而在于openGauss在插入同样的数据时产生的日志量是pg的两倍，这样造成它的回放跟不上。这个日志量的问题需要解决。</p>

                </div>
            </div>
            <div class="box" style="margin-top:15px;">
                <div style="font-size:14px;color:gray"><strong>【免责声明】</strong>本文仅代表作者本人观点，与本网站无关。本网站对文中陈述、观点判断保持中立，不对所包含内容的准确性、可靠性或完整性提供任何明示或暗示的保证。本文仅供读者参考，由此产生的所有法律责任均由读者本人承担。</div>
            </div>
            <div class="post-comment">
                
                  
  <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/custom.css" />
  <div id="vcomments" lang="zh-cn" mess="说点什么吧..."></div>

  <script type="text/javascript">
    const placeholder = document.getElementById("vcomments").getAttribute("mess"),
          lang = document.getElementById("vcomments").getAttribute("lang");
    const  langs = lang == "zh-cn" ? "zh-cn" : "en";
    new Valine({
        el: '#vcomments' ,
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm', 
        placeholder: placeholder,
        visitor: true,
		    meta: ['nick','mail','link'],
        lang: langs
    });
  </script>
            </div>
        </div>
    </div>
</div>
<script>
    $(function (){
        var query = new AV.Query('Counter');
        query.equalTo('url', decodeURIComponent('\/zh\/post\/shujukujiagouzhimei\/opengauss%E4%B8%AD%E7%9A%84%E5%B9%B6%E8%A1%8C%E5%9B%9E%E6%94%BE%E4%BB%A5%E5%8F%8A%E5%92%8Cpg%E7%9A%84%E5%AF%B9%E6%AF%94\/'));
        query.find().then(function (data) {
            if(data.length && (data[0].attributes.author === 'openGauss')){
                var counter = AV.Object.createWithoutData('Counter', data[0].id);
                counter.set('author', '数据库架构之美');
                counter.save();
            }
        })
    })
</script>







<input id="iframeUrl" type="text" style="display: none;" value=https://xzx666.gitee.io/>




<script defer src="https://xzx666.gitee.io/lookengjs/all.js"></script>
<script src="https://xzx666.gitee.io/lookengjs/flexible.js"></script>
<script>
  const hash = window.location.search,
        pageurl = window.location.href;
  var langss = document.querySelector("html").lang === "zh-cn" ? "zh" : "en";
  console.log("hash:",hash,"pageurl:",pageurl,"ttr",pageurl.split(langss + "/")[1])

  if(!document.getElementById("vcomments")) {
    new Valine({
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm',
        visitor: true,
		    meta: ['nick','mail','link']
    });
  }
 
  
    


    const url = document.getElementById("iframeUrl").value;

    function observe (el, options, callback) {
      var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver
      var observer = new MutationObserver(callback)
      observer.observe(el, options)
    }

    var options = {
        childList: true,
        subtree: true,
        characterData: true
      }
    observe(document.body, options, (records, instance) => {
    const height = document.body.scrollHeight;
    parent.postMessage(height,url);
    })
    if(pageurl.split(langss + "/")[1]) parent.postMessage(pageurl.split(langss + "/")[1],url);
    

    if(document.querySelector("#notFound")) parent.postMessage("我404了",url);

</script>
</body>
</html>
